Categories of SQL Commands: DDL, DML, DQL, DCL, and TCL

SQL (Structured Query Language) commands are used to create database structures, insert and modify records, retrieve information, control user permissions, and manage transactions. In MySQL, SQL commands are commonly explained through five categories: DDL, DML, DQL, DCL, and TCL. Each category has a specific purpose in database management.

Course Resource: JustAcademy MySQL Training Course | Register for MySQL Course Demo

1. Learning Objectives

After studying this topic, learners will be able to:

• Understand the five major categories of SQL commands.
• Explain the difference between DDL, DML, DQL, DCL, and TCL.
• Use CREATE, ALTER, DROP, TRUNCATE, INSERT, UPDATE, DELETE, and SELECT.
• Understand how GRANT and REVOKE manage database permissions.
• Use COMMIT, ROLLBACK, and SAVEPOINT to manage transactions.
• Apply SQL commands in practical MySQL database operations.

2. What Are SQL Command Categories?

SQL command categories group statements according to their purpose. Some commands define the structure of database objects, some change the records stored inside tables, some retrieve information, some manage privileges, and others control transactions.

Important: DQL is often taught as a separate category for SELECT, although some references group SELECT under DML. In these notes, the five-category classification is used for learning clarity.

3. DDL – Data Definition Language

3.1 Definition

DDL stands for Data Definition Language. It is used to create, modify, rename, or remove database objects, such as databases, tables, indexes, and views. DDL primarily affects the structure or schema of a database rather than individual records.

3.2 Common DDL Commands

3.3 CREATE Command

The CREATE command creates a new database or database object.

CREATE DATABASE training_db;
USE training_db;
CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL,
    age INT,
    course VARCHAR(100)
);

Explanation: The first statement creates a database named training_db. The USE statement selects that database. The CREATE TABLE statement creates a table with four columns, including a primary key.

3.4 ALTER Command

ALTER modifies the structure of an existing table, such as adding a column.

ALTER TABLE students
ADD email VARCHAR(100);

This statement adds an email column to the students table.

To modify an existing column's data type in MySQL, use appropriate ALTER TABLE syntax, such as:

ALTER TABLE students
MODIFY COLUMN student_name VARCHAR(150) NOT NULL;

3.5 DROP Command

DROP permanently removes a database object, including its definition and, in the case of a table, its stored data.

DROP TABLE students;

Warning: Do not run DROP TABLE on a table containing important data unless removing it is intentional and appropriate backups are available.

3.6 TRUNCATE Command

TRUNCATE removes all rows from a table while keeping the table definition.

TRUNCATE TABLE students;

Use TRUNCATE when the intention is to empty the entire table. In MySQL, TRUNCATE causes an implicit commit and generally cannot be undone with ROLLBACK.

3.7 DDL Characteristics

• Works primarily with database structures and objects.
• Can create, change, or remove tables and other objects.
• MySQL DDL statements commonly cause implicit commits.
• DDL changes should be tested carefully before being used in production.

4. DML – Data Manipulation Language

4.1 Definition

DML stands for Data Manipulation Language. It is used to insert new records, modify existing records, and delete records from database tables. Unlike DDL, DML primarily operates on the data stored inside an existing table.

4.2 Common DML Commands

4.3 INSERT Command

INSERT adds new records to a table. Specifying column names makes the statement easier to understand and maintain.

INSERT INTO students
(student_id, student_name, age, course)
VALUES
(1, 'Aarav Sharma', 21, 'MySQL'),
(2, 'Priya Patel', 22, 'Data Analytics'),
(3, 'Rahul Verma', 20, 'Web Development');

This statement inserts three records into the students table.

4.4 UPDATE Command

UPDATE changes one or more values in existing records.

UPDATE students
SET age = 23
WHERE student_id = 2;

This statement changes the age of the student whose ID is 2.

Important: Always check the WHERE condition before executing UPDATE. Without WHERE, every row in the table may be updated.

4.5 DELETE Command

DELETE removes records from a table.

DELETE FROM students
WHERE student_id = 3;

This statement deletes the record with student ID 3.

To remove every row while keeping the table definition, you can use DELETE FROM students;. Unlike TRUNCATE, DELETE supports a WHERE condition to target specific records. Whether a deletion can be rolled back depends on the storage engine and transaction state; with InnoDB, an uncommitted DELETE can be rolled back.

4.6 DML Characteristics

• Works with records inside existing database objects.
• Supports adding, modifying, and deleting records.
• In transactional tables such as InnoDB, DML changes can generally be rolled back before COMMIT.
• WHERE conditions help target the intended records.

5. DQL – Data Query Language

5.1 Definition

DQL stands for Data Query Language. It is used to retrieve information from a database without directly changing the stored records. SELECT is the main command associated with DQL.

5.2 SELECT Command

SELECT retrieves columns and rows from one or more tables.

SELECT * FROM students;

This query returns all columns and rows from the students table.

5.3 Selecting Specific Columns

SELECT student_name, course
FROM students;

This query returns only the student_name and course columns.

5.4 Filtering Records with WHERE

SELECT *
FROM students
WHERE age >= 21;

This query returns students whose age is 21 or above.

5.5 Sorting Results with ORDER BY

SELECT student_name, age
FROM students
ORDER BY age DESC;

ORDER BY sorts the returned records by age, from highest to lowest. Use ASC for ascending order and DESC for descending order.

5.6 Grouping Data with GROUP BY

SELECT course, COUNT(*) AS total_students
FROM students
GROUP BY course;

This query groups students by course and counts the records in each group.

5.7 Filtering Groups with HAVING

SELECT course, COUNT(*) AS total_students
FROM students
GROUP BY course
HAVING COUNT(*) > 1;

HAVING filters groups after aggregation, whereas WHERE filters individual rows before grouping.

5.8 DQL Characteristics

• Retrieves information from tables or views.
• Supports filtering, sorting, grouping, and aggregation.
• SELECT alone does not modify table records.
• Queries can use joins and subqueries to retrieve related information.

6. DCL – Data Control Language

6.1 Definition

DCL stands for Data Control Language. It is associated with controlling access to database objects and managing the privileges assigned to users. The primary commands commonly taught are GRANT and REVOKE.

6.2 GRANT Command

GRANT gives a database account specific privileges.

GRANT SELECT
ON training_db.students
TO 'report_user'@'localhost';

This example grants SELECT permission on the students table to the MySQL account 'report_user'@'localhost', assuming that the account exists and the executing account has sufficient privileges.

6.3 REVOKE Command

REVOKE removes specified privileges from a user account.

REVOKE SELECT
ON training_db.students
FROM 'report_user'@'localhost';

This statement revokes the SELECT privilege on the students table from that account.

6.4 Other Common Privileges

6.5 DCL Characteristics

• Helps protect sensitive database information.
• Allows administrators to apply the principle of least privilege.
• GRANT assigns privileges, while REVOKE removes them.
• Exact permissions depend on the user account, object scope, and MySQL configuration.

Security best practice: Avoid granting broad privileges to ordinary application accounts. Give each account only the permissions it needs.

7. TCL – Transaction Control Language

7.1 Definition

TCL stands for Transaction Control Language. It manages transactions, which are groups of database operations treated as a unit of work. TCL helps control whether transactional changes are saved or undone.

7.2 COMMIT Command

COMMIT makes the current transaction's changes permanent.

START TRANSACTION;
UPDATE students
SET age = 22
WHERE student_id = 1;
COMMIT;

The transaction begins, updates the age for student ID 1, and then commits the change.

7.3 ROLLBACK Command

ROLLBACK undoes uncommitted changes in the current transaction when the storage engine and statement types support rollback.

START TRANSACTION;
UPDATE students
SET age = 25
WHERE student_id = 1;
ROLLBACK;

The update is undone if it remains uncommitted and is supported by the transactional storage engine, such as InnoDB.

7.4 SAVEPOINT Command

SAVEPOINT creates a named point inside a transaction to which you can roll back without necessarily undoing the entire transaction.

START TRANSACTION;
UPDATE students
SET age = 22
WHERE student_id = 1;
SAVEPOINT after_first_update;
UPDATE students
SET age = 24
WHERE student_id = 2;
ROLLBACK TO SAVEPOINT after_first_update;
COMMIT;

The second update is undone by the rollback to the savepoint, while the first update remains and is committed.

7.5 RELEASE SAVEPOINT

RELEASE SAVEPOINT removes a savepoint from the current transaction.

START TRANSACTION;
SAVEPOINT my_point;
UPDATE students
SET age = 21
WHERE student_id = 1;
RELEASE SAVEPOINT my_point;
COMMIT;

Releasing a savepoint does not commit the transaction. The transaction still needs to be committed or rolled back.

7.6 TCL Characteristics

• Controls transactional changes.
• COMMIT saves supported changes permanently.
• ROLLBACK undoes supported uncommitted changes.
• SAVEPOINT enables partial rollback within a transaction.
• Transactions are particularly useful for financial operations and other multi-step updates that must remain consistent.

MySQL note: Transaction behavior depends on the storage engine. InnoDB supports transactions, while nontransactional engines do not provide the same rollback guarantees. Many DDL statements also cause implicit commits, so they cannot be treated like ordinary rollbackable DML statements.

8. Difference Between DDL, DML, DQL, DCL, and TCL

9. Difference Between DELETE, TRUNCATE, and DROP

These three commands are often confused because each can result in data being removed. Their purposes and effects are different.

Example

-- Remove only one record
DELETE FROM students WHERE student_id = 1;

-- Remove all rows but retain the table
TRUNCATE TABLE students;

-- Remove the entire table
DROP TABLE students;

Run these examples separately and only when the relevant deletion is intentional.

10. Practical MySQL Example Using All Five Categories

The following example demonstrates the five categories in a simple student database. Run the statements in a test database and adapt the account and permission examples to your MySQL environment.

Step 1: DDL – Create the Database and Table

CREATE DATABASE sql_categories_demo;
USE sql_categories_demo;
CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL,
    age INT,
    course VARCHAR(100)
) ENGINE=InnoDB;

Step 2: DML – Insert and Update Records

INSERT INTO students
(student_id, student_name, age, course)
VALUES
(1, 'Aarav', 21, 'MySQL'),
(2, 'Priya', 22, 'SQL'),
(3, 'Rahul', 20, 'MySQL');

UPDATE students
SET age = 23
WHERE student_id = 2;

Step 3: DQL – Retrieve Records

SELECT *
FROM students;

SELECT student_name, course
FROM students
WHERE age >= 21
ORDER BY student_name ASC;

Step 4: DCL – Grant and Revoke Permissions

-- Example syntax; requires suitable privileges.
GRANT SELECT
ON sql_categories_demo.students
TO 'report_user'@'localhost';

REVOKE SELECT
ON sql_categories_demo.students
FROM 'report_user'@'localhost';

The example account must exist and the executing account must have permission to grant or revoke the specified privilege. Account creation and authentication are separate administrative tasks.

Step 5: TCL – Commit or Roll Back a Change

START TRANSACTION;
UPDATE students
SET age = 22
WHERE student_id = 1;
ROLLBACK;

START TRANSACTION;
UPDATE students
SET age = 22
WHERE student_id = 1;
COMMIT;

The first transaction demonstrates undoing the update. The second demonstrates saving the update. These examples assume a transactional table such as InnoDB and no intervening implicit commit.

11. Common Mistakes and How to Avoid Them

12. Best Practices for SQL Command Categories

• Use DDL carefully, especially DROP and TRUNCATE.
• Use WHERE clauses for targeted UPDATE and DELETE operations.
• Use SELECT to inspect records before changing or removing them.
• Use transactions for related multi-step changes when supported.
• Commit only after verifying that the intended changes are correct.
• Use ROLLBACK when you need to undo supported uncommitted changes.
• Apply the principle of least privilege when using GRANT.
• Test commands in a development environment before executing them in production.
• Maintain reliable backups and follow your organization's recovery procedures.
• Use clear, consistent table and column names.

13. Frequently Asked Questions (FAQs)

Q1. What are the five categories of SQL commands?

The five commonly taught categories are DDL, DML, DQL, DCL, and TCL.

Q2. What is the full form of DDL?

DDL stands for Data Definition Language. It defines and modifies database structures.

Q3. What is the full form of DML?

DML stands for Data Manipulation Language. It is used to insert, update, and delete records.

Q4. Which SQL command is commonly associated with DQL?

SELECT is the main command associated with Data Query Language.

Q5. What is the difference between DDL and DML?

DDL mainly changes database structures, whereas DML changes records stored in tables.

Q6. What are the main DCL commands?

GRANT assigns privileges, and REVOKE removes privileges.

Q7. What is the purpose of TCL?

TCL controls transactions using commands such as COMMIT, ROLLBACK, and SAVEPOINT.

Q8. Can ROLLBACK undo a DELETE statement?

Yes, if the DELETE is part of an uncommitted transaction and the storage engine supports transactions, as InnoDB does. Once the transaction is committed, ROLLBACK cannot undo that committed change.

Q9. Is TRUNCATE a DDL or DML command in MySQL?

TRUNCATE is classified as DDL in MySQL. It removes all rows while retaining the table structure and causes an implicit commit.

Q10. Why is DQL sometimes included under DML?

Some learning materials use four broad categories and group SELECT with DML. Other materials separate SELECT into DQL to highlight its data-retrieval purpose.

Q11. Which command removes a table completely?

DROP TABLE removes the table definition and its stored data.

Q12. Which category manages database permissions?

DCL manages database privileges through commands such as GRANT and REVOKE.

14. Practice Questions

1. Write the full forms of DDL, DML, DQL, DCL, and TCL.
2. Which category contains CREATE TABLE?
3. Write a query to add a new student record.
4. Write an UPDATE statement that changes a student's course using their ID.
5. Write a SELECT query to find students older than 20.
6. Explain the difference between DELETE, TRUNCATE, and DROP.
7. Write an example of GRANT and explain its purpose.
8. What is the difference between COMMIT and ROLLBACK?
9. How does SAVEPOINT help during a transaction?
10. Why should UPDATE and DELETE statements usually include an appropriate WHERE clause?

Practical Exercise

Create a database called college_db and a table called courses. Include course_id, course_name, duration_months, and fees columns. Insert at least three records, update one course fee, retrieve courses whose fees exceed a chosen amount, and use a transaction to demonstrate COMMIT or ROLLBACK. Practice GRANT and REVOKE only in an account where you have the necessary administrative privileges.

15. Quick Revision

• DDL = Structure: CREATE, ALTER, DROP, TRUNCATE.
• DML = Data changes: INSERT, UPDATE, DELETE.
• DQL = Data retrieval: SELECT.
• DCL = Access control: GRANT, REVOKE.
• TCL = Transaction control: COMMIT, ROLLBACK, SAVEPOINT.

A simple way to remember these categories is: define the structure, manipulate the records, query the data, control access, and manage transactions. Understanding the purpose and behavior of each category helps you write safer and more effective MySQL statements.

16. Conclusion

SQL command categories provide a clear framework for understanding database operations. DDL manages structures, DML modifies records, DQL retrieves information, DCL controls access privileges, and TCL manages transactional changes. By practicing these commands in a test MySQL database and learning the behavior of transactions and implicit commits, learners can build a strong foundation for database development and administration.

Explore more learning resources through the JustAcademy MySQL Training Course or Register for MySQL Course Demo.